/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Model;

import Data.ChuyenTau;
import Data.Tuyen;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author bienphongvnn
 */
public class TuyenModel {
    
     public static ArrayList<Tuyen> ListTuyenTau()  {	
        ArrayList<Tuyen> ds = new ArrayList<Tuyen>();
        ArrayList<String> ds2 = new ArrayList<String>();
        ArrayList<String> ds3 = new ArrayList<String>();
        Connection con = ConnectionProvider.Connect();
	try {
            
            String sql = "SELECT tt.MaTuyen,tt.TenTuyen,ng.TenNhaGa FROM tuyentau tt, nhaga ng WHERE ng.MaNhaGa = tt.GaKhoiHanh ";
            Statement statement	= con.createStatement();		
            ResultSet rs = statement.executeQuery(sql);	
              while(rs.next()){	
                
               Tuyen tt = new Tuyen();
               tt.MaTuyen = rs.getString("tt.MaTuyen");
               tt.TenTuyen= rs.getString("tt.TenTuyen");
               tt.GaDi= rs.getString("ng.TenNhaGa");
              
                              
               	 
                
             ds.add(tt);
                }
              
            String sql2 = "SELECT ng.TenNhaGa FROM tuyentau tt, nhaga ng WHERE ng.MaNhaGa = tt.GaKetThuc ";
            Statement statement2	= con.createStatement();		
            ResultSet rs2 = statement2.executeQuery(sql2);
          while(rs2.next()){
              String a = rs2.getString("ng.TenNhaGa");
              ds2.add(a);
            
          }
             con.close();
             for(int i =ds2.size() - 1 ;i >=0 ;i--)
             {
                 ds3.add(ds2.get(i)) ;
             }
             for(int i = 0;i<ds.size();i++)
             {
                 ds.get(i).GaDen = ds3.get(i);
             }
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());	
        }
        finally
        {
          
        }
        return	ds;	
    }
     
     public static void Them(Tuyen n) throws SQLException{
         Connection conn = ConnectionProvider.Connect();
        try{
          
            String sql = "INSERT INTO tuyentau(MaTuyen, TenTuyen, GaKhoiHanh,GaKetThuc) VALUES(?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, n.MaTuyen);
            pst.setString(2, n.TenTuyen);
            pst.setString(3, n.GaDi);
            pst.setString(4, n.GaDen);
            pst.executeUpdate();
            pst.close();
            JOptionPane.showMessageDialog(null, "Thêm thành công!!!","Thông báo", 1); 
        }
        catch(Exception ex){
            ex.printStackTrace();
            ex.getMessage();
        }
        finally
        {
                conn.close();
        }
    }
     public static  void Xoa(String MaTuyen) throws SQLException{
          Connection conn = ConnectionProvider.Connect(); 
        try{
            
            String strSQL = "delete from tuyentau where MaTuyen = ?";
            PreparedStatement pst = conn.prepareStatement(strSQL);
            pst.setString(1, MaTuyen);
            pst.executeUpdate();
            pst.close();
        }
        catch(Exception ex){
            ex.printStackTrace();
            ex.getMessage(); 
            JOptionPane.showMessageDialog(null, ex.getStackTrace(), "Thông báo lỗi", JOptionPane.OK_OPTION);
        }
        finally
        {
            conn.close();
        }
    }
      public static void CapNhat(Tuyen n) throws SQLException{
         Connection conn = ConnectionProvider.Connect();
        try{
            String sql = "UPDATE tuyentau Set TenTuyen = ?,GaKhoiHanh = ?,GaKetThuc = ? Where MaTuyen = ?";
 
            PreparedStatement pst = conn.prepareStatement(sql);
           
            pst.setString(1, n.TenTuyen);
            pst.setString(2, n.GaDi);
            pst.setString(3, n.GaDen);
            pst.setString(4, n.MaTuyen);
            pst.executeUpdate();
            pst.close();
            JOptionPane.showMessageDialog(null, "Cập Nhật thành công!!!","Thông báo", 1);
        }
        catch(Exception ex){
            ex.printStackTrace();
            ex.getMessage();
        }
        finally
        {
                conn.close();
        }
    }
}
